package com.group11.studentm.service;

import com.group11.studentm.dao.BasicDao;
import com.group11.studentm.domain.Course;
import com.group11.studentm.domain.StuSelectScoreT;
import com.group11.studentm.domain.Sdept;

import java.util.List;

/**
 * 教师管理页面业务
 */
public class TeacherService {
    private BasicDao dao=new BasicDao();

    /**
     * 根据课程名查找对应的课程id
     * @param cname 课程名
     * @return
     */
    public int findCID(String cname){

        Object queryScalar = dao.queryScalar("select Cno from course where Cname=?", cname);
        if (queryScalar==null) {
            return 0;
        }
        return (int) queryScalar;
    }

    /**
     * 添加学生课程得分
     * @param sno 学号
     * @param cname 所学课程名
     * @param grade 得分
     * @return
     */
    public boolean addSc(String sno,int cname , int grade){
        int update = 0;
        try {
            update = dao.update("insert into sc values(?,?,?)",sno,cname,grade);
        } catch (Exception e) {
            System.out.println("课程添加失败");
        }
        return update>0;
    }

    /**
     * 查询返回所有课程
     * @return
     */
    public List<Course> findCourse(){
        List<Course> list = dao.queryMulti("select * from course", Course.class);
        return list;
    }
    /**
     * 查询返回院系列表
     * @return
     */
    public List<Sdept> findAllSName(){
        List<Sdept> list = dao.queryMulti("select * from sdept", Sdept.class);
        return list;
    }

    /**
     * 无学号情况下
     * 所有院系所有课程
     * @return
     */
    public List<StuSelectScoreT> findAllSc(){
        List<StuSelectScoreT> list = dao.queryMulti("SELECT sc.`Sno`,student.`Sname`,course.`Cname`,sc.`Grade` " +
                " FROM sc,student,course WHERE sc.`Sno`=student.`Sno` AND sc.`Cno`=course.`Cno` ORDER BY sc.`Sno`; ",StuSelectScoreT.class);
        return list;
    }

    /**
     * 无学号情况下
     * 所有院系某一课程
     * @param cname
     * @return
     */
    public List<StuSelectScoreT> findAllSc1(String cname){
        List<StuSelectScoreT> list = dao.queryMulti("SELECT sc.`Sno`,student.`Sname`,course.`Cname`,sc.`Grade` " +
                " FROM sc,student,course WHERE sc.`Sno`=student.`Sno` AND sc.`Cno`=course.`Cno` and Cname=?; ",StuSelectScoreT.class,cname);
        return list;
    }

    /**
     * 无学号情况下
     * 某一学院所有课程
     * @param sdept_name
     * @return
     */
    public List<StuSelectScoreT> findAllSc2(String sdept_name){
        List<StuSelectScoreT> list = dao.queryMulti("SELECT sc.`Sno`,student.`Sname`,course.`Cname`,sc.`Grade`" +
                " FROM sc,student,course,sdept " +
                " WHERE sc.`Sno`=student.`Sno` AND sc.`Cno`=course.`Cno` AND sdept.`id`=student.`Sdept_id` " +
                " AND sdept.`sdept_name`=?;", StuSelectScoreT.class, sdept_name);
        return list;
    }

    /**
     * 无学号情况下
     * 某一学院某一课程
     * @param sdept_name 学院
     * @param Cname 课程
     * @return
     */
    public List<StuSelectScoreT> findAllSc3(String sdept_name,String Cname){
        List<StuSelectScoreT> list = dao.queryMulti("SELECT sc.`Sno`,student.`Sname`,course.`Cname`,sc.`Grade`" +
                " FROM sc,student,course,sdept " +
                " WHERE sc.`Sno`=student.`Sno` AND sc.`Cno`=course.`Cno` AND sdept.`id`=student.`Sdept_id` " +
                " AND sdept.`sdept_name`=? AND course.`Cname`=?;", StuSelectScoreT.class, sdept_name,Cname);
        return list;
    }

    /**
     * 有学号的情况下
     * 院系信息已经无影响了
     * 查询该学生全部课程
     * @param sno 学号
     * @return
     */
    public List<StuSelectScoreT> findGrade1(String sno){
        List<StuSelectScoreT> list = dao.queryMulti("SELECT sc.`Sno`,student.`Sname`,course.`Cname`,sc.`Grade` " +
                "FROM sc,student,course WHERE sc.`Sno`=student.`Sno` AND sc.`Cno`=course.`Cno` AND sc.`Sno`=?;", StuSelectScoreT.class, sno);
        return list;
    }

    /**
     *有学号的情况下
     *院系信息已经无影响了
     *查询该学生某一课程成绩
     * @param sno 学号
     * @param cname 课程名
     * @return
     */
    public List<StuSelectScoreT> findGrade2(String sno,String cname){
        List<StuSelectScoreT> list = dao.queryMulti("SELECT sc.`Sno`,student.`Sname`,course.`Cname`,sc.`Grade` " +
                "FROM sc,student,course WHERE sc.`Sno`=student.`Sno` AND sc.`Cno`=course.`Cno` " +
                "AND sc.`Sno`=? and course.`Cname`=?;", StuSelectScoreT.class, sno,cname);
        return list;
    }
    /**
     * 检查账号是否存在数据库
     * @param Sno 学号
     * @return
     */
    public boolean checkSno(String Sno) {
        String sno = (String) dao.queryScalar("select sno from user where sno=?", Sno);
        return sno!=null;
    }


}
